package com.warrior.superdata.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.warrior.superdata.base.BasePageResultVo;
import com.warrior.superdata.constants.CommonConstant;
import com.warrior.superdata.constants.ResultCode;
import com.warrior.superdata.dds.DataSourceHelp;
import com.warrior.superdata.enums.DatasourceType;
import com.warrior.superdata.enums.OperateType;
import com.warrior.superdata.handler.SqlHandler;
import com.warrior.superdata.mysql.entity.dto.ApiDatasourceDto;
import com.warrior.superdata.mysql.entity.dto.ApiResultDto;
import com.warrior.superdata.mysql.entity.dto.TableFieldDto;
import com.warrior.superdata.service.QuerySqlService;
import com.warrior.superdata.exceptionhandler.ApplicationException;
import com.warrior.superdata.mysql.entity.dto.ApiInfoDetailDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

/**
 * @Description:
 * @ClassName: com.warrior.superdata.service.MysqlExecuteSqlServiceImpl.java
 * @date: 2022/3/2 15:02
 * @author xujian
 */
@Service
@Slf4j
public class MysqlQuerySqlServiceImpl implements QuerySqlService {

    @Autowired
    private DataSourceHelp dataSourceHelp;

    @Autowired
    private SqlHandler sqlHandler;

    @Override
    public String getDatasourceType() {
        return DatasourceType.MYSQL.getName();
    }

    @Override
    public String getOperateType() {
        return OperateType.QUERY.getName();
    }

    @Override
    public Object execute(ApiInfoDetailDto apiInfoDetailDto) throws ApplicationException {
        String sql = sqlHandler.generateSql(apiInfoDetailDto);

        ApiDatasourceDto datasourceDto = apiInfoDetailDto.getDatasourceDto();
        if (null == datasourceDto) {
            throw new ApplicationException(ResultCode.FAIL_CODE, "数据源获取异常");
        }
        Connection connect = dataSourceHelp.getConnect(datasourceDto);
        Statement statement = null;
        JSONArray result = new JSONArray();
        BasePageResultVo pageResultVo = new BasePageResultVo();
        try {
            statement = connect.createStatement();
            statement.execute("use " + apiInfoDetailDto.getDatabaseName());
            if (CommonConstant.API_DATA_RESULT_PAGE == apiInfoDetailDto.getResultType()) {
                String countSql = sqlHandler.generateCountSql(apiInfoDetailDto);
                ResultSet countResult = statement.executeQuery(countSql);
                while (countResult.next()) {
                    int count = countResult.getInt(1);
                    pageResultVo.setTotal(count);
                    pageResultVo.setPage(apiInfoDetailDto.getPage());
                    pageResultVo.setSize(apiInfoDetailDto.getSize());
                }
            }
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                List<ApiResultDto> results = apiInfoDetailDto.getResults();
                results.forEach(r -> {
                    try {
                        String value = resultSet.getString(r.getResultName());
                        jsonObject.put(r.getResultName(), value);
                    } catch (Exception e) {
                        log.error("字段赋值异常 errMsg:{}", e.getMessage());
                    }
                });
                result.add(jsonObject);
            }
        } catch (Exception e) {
            log.error("mysql执行sql异常 errMsg:{}", e.getMessage());
        } finally {
            try {
                if (null != statement) {
                    statement.close();
                }
                if (null != connect) {
                    connect.close();
                }
            } catch (Exception e) {
                log.error("关流异常 errMsg:{}", e.getMessage());
            }
        }
        if (CommonConstant.API_DATA_RESULT_ARR == apiInfoDetailDto.getResultType()) {
            return result;
        } else if (CommonConstant.API_DATA_RESULT_PAGE == apiInfoDetailDto.getResultType()) {
            pageResultVo.setContent(result);
            return pageResultVo;
        } else if (CommonConstant.API_DATA_RESULT_SINGLE == apiInfoDetailDto.getResultType()
                && result.size() > 0) {
            return result.get(0);
        }
        return null;
    }

    @Override
    public List<String> testSourceConnect(ApiDatasourceDto apiDatasourceDto) throws ApplicationException {
        List<String> result = new ArrayList<>();
        Connection connect = null;
        Statement statement = null;
        try {
            connect = dataSourceHelp.getConnect(apiDatasourceDto);
            statement = connect.createStatement();
            ResultSet resultSet = statement.executeQuery("show databases");
            while (resultSet.next()) {
                String databaseName = resultSet.getString("Database");
                result.add(databaseName);
            }
        } catch (Exception e) {
            throw new ApplicationException(ResultCode.FAIL_CODE, "数据库连接异常 msg:" + e.getMessage());
        } finally {
            dataSourceHelp.close(statement, connect);
        }
        return result;
    }

    @Override
    public Set<String> getTables(ApiDatasourceDto apiDatasourceDto) {
        Set<String> result = new LinkedHashSet<>();
        Statement statement = null;
        Connection connect = null;
        try {
            connect = dataSourceHelp.getConnect(apiDatasourceDto);
            statement = connect.createStatement();
            statement.execute("use " + apiDatasourceDto.getDatabaseName());
            ResultSet resultSet = statement.executeQuery("show tables");
            while (resultSet.next()) {
                String databaseName = resultSet.getString("tables_in_" + apiDatasourceDto.getDatabaseName());
                result.add(databaseName);
            }
        } catch (Exception e) {
            log.error("获取表列表异常 errMsg:{}", e.getMessage());
        } finally {
            dataSourceHelp.close(statement, connect);
        }
        return result;
    }

    public List<TableFieldDto> getTableFields(ApiDatasourceDto apiDatasourceDto, String tableName) {
        List<TableFieldDto> result = new ArrayList<>();
        Statement statement = null;
        Connection connect = null;
        try {
            connect = dataSourceHelp.getConnect(apiDatasourceDto);
            statement = connect.createStatement();
            statement.execute("use " + apiDatasourceDto.getDatabaseName());
            ResultSet resultSet = statement.executeQuery("show full columns from " + tableName);
            while (resultSet.next()) {
                TableFieldDto fieldDto = new TableFieldDto();
                String fieldName = resultSet.getString("Field");
                String fieldType = resultSet.getString("Type");
                fieldDto.setFieldName(fieldName);
                fieldDto.setFieldType(fieldType);
                result.add(fieldDto);
            }
        } catch (Exception e) {
            log.error("获取字段列表异常 errMsg:{}", e.getMessage());
        }
        return result;
    }

}
